package com.efesco.stat.insurance;

import com.bstek.dorado.data.*;
import com.bstek.dorado.data.db.SqlDataset;
import com.bstek.dorado.common.*;
import com.efesco.common.Tools;

/**
 * ContractServiceBanlance_datasetAnnListener
 */
public class ContractServiceBanlance_datasetAnnListener extends AbstractDatasetListener {
  public boolean beforeLoadData(Dataset dataset) throws Exception {
	  SqlDataset ds=(SqlDataset)dataset;
	  String sql="select distinct emp.employee_name,emp.employee_code,com.company_code,com.company_chinese_name,"
                +"ecfi.personal_base,anni.pay_base "
                +"from s_employee emp,s_single_company com,s_employee_on_off_post eoop,s_employee_contract_fee ecf," 
                +"s_employee_contract_fee_item ecfi,s_annuity ann,s_annuity_item anni "
                +"where eoop.employee_id=emp.employee_id and eoop.single_company_id=com.single_company_id "
                +"and eoop.employee_on_off_id=ecf.employee_on_off_id "
                +"and ecf.employee_contract_fee_id=ecfi.employee_contract_fee_id "
                +"and eoop.employee_on_off_id=ann.employee_on_off_id "
                +"and ann.annuity_id=anni.annuity_id "
                +"and ann.annuity_id=(select max(annuity_id) from s_annuity "
                +"where employee_on_off_id=eoop.employee_on_off_id and (end_pay_month is null or start_pay_month<=end_pay_month))"
                +"and eoop.employ_start_date<=eoop.employ_end_date "
                +"and ecf.start_date<=ecf.end_date "
                +"and ecf.start_date=(select max(start_date) from s_employee_contract_fee "
                +"where employee_on_off_id=eoop.employee_on_off_id and start_date<=end_date) "
                +"and ecfi.product_id=anni.product_id and ecfi.personal_base<>anni.pay_base ";
	  String empName=Tools.toStr(ds.parameters().getString("employee_name"));
	  if(!"".equals(empName)){
		  sql+=" and emp.employee_name like '%"+empName+"%'";
	  }
	  String empCode=Tools.toStr(ds.parameters().getString("employee_code"));
	  if(!"".equals(empCode)){
		  sql+=" and emp.employee_code like '%"+empCode+"%'";
	  }
	  String comCode=Tools.toStr(ds.parameters().getString("company_code"));
	  if(!"".equals(comCode)){
		  sql+=" and com.company_code like '%"+comCode+"%'";
	  }
	  String comName=Tools.toStr(ds.parameters().getString("company_name"));
	  if(!"".equals(comName)){
		  sql+=" and com.company_chinese_name like '%"+comName+"%'";
	  }
	  ds.setSql(sql);
      return true;
  }

  public void afterLoadData(Dataset dataset)
     throws Exception {

  }

}
